home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Software Vault: The Gold Collection
/
Software Vault - The Gold Collection (American Databankers) (1993).ISO
/
cdr11
/
pdox693.zip
/
TI553.ASC
< prev
next >
Wrap
Text File
|
1992-12-03
|
8KB
|
265 lines
PRODUCT : Paradox NUMBER : 553
VERSION : 3.0 & up
OS : DOS
DATE : December 3, 1992 PAGE : 1/4
TITLE : Using Query and Crosstab features to age accounts by month
This Technical Information sheet illustrates how you can use the Query
and Crosstab features to age accounts by month from a single date
field.
Suppose we wish to perform an account aging on the table pictured
below called Aging:
AGING══╦══════Acct#═══╦═════Date═════╦═══════Amt════════╗
1 ║ 1234 ║ 2/12/85 ║ 750.00 ║
2 ║ 4288 ║ 3/01/85 ║ 2,500.00 ║
3 ║ 7885 ║ 6/12/85 ║ 3,590.00 ║
4 ║ 3695 ║ 3/03/85 ║ 895.00 ║
5 ║ 1234 ║ 3/15/85 ║ 350.00 ║
6 ║ 7885 ║ 3/15/85 ║ 235.00 ║
7 ║ 1234 ║ 3/18/85 ║ 350.00 ║
8 ║ 1234 ║ 3/30/85 ║ 100.00 ║
9 ║ 3695 ║ 2/12/85 ║ 325.00 ║
10 ║ 4288 ║ 3/29/85 ║ 650.00 ║
11 ║ 7885 ║ 4/03/85 ║ 340.00 ║
12 ║ 4288 ║ 3/29/85 ║ 1,250.00 ║
13 ║ 3695 ║ 2/21/85 ║ 990.00 ║
14 ║ 3695 ║ 4/01/85 ║ 2,100.00 ║
15 ║ 4288 ║ 2/28/85 ║ 21.00 ║
16 ║ 3695 ║ 4/12/85 ║ 2,350.00 ║
17 ║ 4288 ║ 2/01/85 ║ 666.00 ║
18 ║ 7885 ║ 2/04/85 ║ 1,000.00 ║
19 ║ 1234 ║ 4/12/85 ║ 235.00 ║
20 ║ 4288 ║ 5/01/85 ║ 5,000.00 ║
21 ║ 3695 ║ 5/02/85 ║ 23.00 ║
22 ║ 4288 ║ 5/13/85 ║ 1,253.00 ║
23 ║ 4288 ║ 6/06/85 ║ 560.00 ║
24 ║ 7885 ║ 3/13/85 ║ 475.00 ║
25 ║ 3695 ║ 6/23/85 ║ 100.00 ║
26 ║ 3695 ║ 6/19/85 ║ 247.00 ║
27 ║ 7885 ║ 2/01/85 ║ 950.00 ║
The objective is to calculate the sum of payments per account and per
month. This can be accomplished by the query pictured on the
following page.
PRODUCT : Paradox NUMBER : 553
VERSION : 3.0 & up
OS : DOS
DATE : December 3, 1992 PAGE : 2/4
TITLE : Using Query and Crosstab features to age accounts by month
AGING══╦Acct#╦═══════════════Date═════════════════╦═════Amt═══════╗
1 ║ √ ║>1/31/85,<3/1/85, calc "Feb" as date║calc sum as amt║
2 ║ √ ║>2/28/85,<4/1/85, calc "Mar" as date║calc sum as amt║
3 ║ √ ║>3/31/85,<5/1/85, calc "Apr" as date║calc sum as amt║
4 ║ √ ║>4/30/85,<6/1/85, calc "May" as date║calc sum as amt║
5 ║ √ ║>5/31/85,<7/1/85, calc "Jun" as date║calc sum as amt║
The query pictured above breaks down the payments by month for each
account by:
1. Selecting each Account number.
2. Specifying the date range which will determine the particular
month (i.e. "Feb", "Mar", etc.) and creating a new field called
"Date".
3. Calculating the sum of the Amt field for the month range
specified in step 2 and creating a field called "amt".
When the query is processed, it produces the following Answer table:
ANSWER═╦═════Acct#═══╦═════Amt═════╦═Date═╗
1 ║ 1234 ║ 235.00 ║ Apr ║
2 ║ 1234 ║ 750.00 ║ Feb ║
3 ║ 1234 ║ 800.00 ║ Mar ║
4 ║ 3695 ║ 23.00 ║ May ║
5 ║ 3695 ║ 347.00 ║ Jun ║
6 ║ 3695 ║ 895.00 ║ Mar ║
7 ║ 3695 ║ 1,315.00 ║ Feb ║
8 ║ 3695 ║ 4,450.00 ║ Apr ║
9 ║ 4288 ║ 560.00 ║ Jun ║
10 ║ 4288 ║ 687.00 ║ Feb ║
11 ║ 4288 ║ 4,400.00 ║ Mar ║
12 ║ 4288 ║ 6,253.00 ║ May ║
13 ║ 7885 ║ 340.00 ║ Apr ║
14 ║ 7885 ║ 710.00 ║ Mar ║
15 ║ 7885 ║ 1,950.00 ║ Feb ║
16 ║ 7885 ║ 3,590.00 ║ Jun ║
PRODUCT : Paradox NUMBER : 553
VERSION : 3.0 & up
OS : DOS
DATE : December 3, 1992 PAGE : 3/4
TITLE : Using Query and Crosstab features to age accounts by month
The next step in processing an account aging is to produce a Crosstab
of the Answer table as follows:
1. With your cursor in the Amt field, rotate the Amt and Date
columns by pressing <Ctrl-R>.
When rotated, the Answer table will look as follows:
ANSWER═╦═════Acct#═══╦═Date═╦═════Amt═════╗
1 ║ 1234 ║ Apr ║ 235.00 ║
2 ║ 1234 ║ Feb ║ 750.00 ║
3 ║ 1234 ║ Mar ║ 800.00 ║
4 ║ 3695 ║ May ║ 23.00 ║
5 ║ 3695 ║ Jun ║ 347.00 ║
6 ║ 3695 ║ Mar ║ 895.00 ║
7 ║ 3695 ║ Feb ║ 1,315.00 ║
8 ║ 3695 ║ Apr ║ 4,450.00 ║
9 ║ 4288 ║ Jun ║ 560.00 ║
10 ║ 4288 ║ Feb ║ 687.00 ║
11 ║ 4288 ║ Mar ║ 4,400.00 ║
12 ║ 4288 ║ May ║ 6,253.00 ║
13 ║ 7885 ║ Apr ║ 340.00 ║
14 ║ 7885 ║ Mar ║ 710.00 ║
15 ║ 7885 ║ Feb ║ 1,950.00 ║
16 ║ 7885 ║ Jun ║ 3,590.00 ║
2. To crosstab the rotated Answer table, press <F10>, then
select Image | Graph | Crosstab | Sum.
3. Place the cursor in the Acct# field, then press <ENTER> to
set the row labels.
4. Move the cursor to the Date field, then press <ENTER> to
set the column labels.
5. Move the cursor to the Amt field, then press <ENTER> to
generate the data to be summed in the Crosstab.
The Crosstab of the Answer table is pictured on the next page.
PRODUCT : Paradox NUMBER : 553
VERSION : 3.0 & up
OS : DOS
DATE : December 3, 1992 PAGE : 4/4
TITLE : Using Query and Crosstab features to age accounts by month
CROSSTAB╦═Acct#═╦═══Apr═══╦═══Feb═══╦═══Mar═══╦═══May═══╦═══Jun═══╗
1 ║ 1234 ║ 235.00║ 750.00║ 800.00║ 0.00║ 0.00║
2 ║ 3695 ║ 4,450.00║ 1,315.00║ 895.00║ 23.00║ 347.00║
3 ║ 4288 ║ 0.00║ 687.00║ 4,400.00║ 6,253.00║ 560.00║
4 ║ 7885 ║ 340.00║ 1,950.00║ 710.00║ 0.00║ 3,590.00║
To order the month fields from Feb to Jun, rotate the fields as
follows:
1. Place the cursor in the Apr field and press <Ctrl-R>.
2. Place the cursor in the May field and press <Ctrl-R> twice.
The Crosstab below shows the activity of each account by month as
follows:
CROSSTAB╦═Acct#═╦═══Feb═══╦═══Mar═══╦═══Apr═══╦═══May═══╦═══Jun═══╗
1 ║ 1234 ║ 750.00║ 800.00║ 235.00║ 0.00║ 0.00║
2 ║ 3695 ║ 1,315.00║ 895.00║ 4,450.00║ 23.00║ 347.00║
3 ║ 4288 ║ 687.00║ 4,400.00║ 0.00║ 6,253.00║ 560.00║
4 ║ 7885 ║ 1,950.00║ 710.00║ 340.00║ 0.00║ 3,590.00║
For additional information on queries, refer to the Chapter on Ask in
the Paradox User's Guide. For information on Crosstabs, refer to the
Chapter on Image in the Paradox User's Guide.
DISCLAIMER: You have the right to use this technical information
subject to the terms of the No-Nonsense License Statement that you
received with the Borland product to which this information pertains.